package org.smart4j.chapter2.helper;


import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.smart4j.chapter2.service.CustomerService;
import org.smart4j.chapter2.util.CollectionUtil;
import org.smart4j.chapter2.util.PropsUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

//数据库操作的工具类
public class DatabaseHelper {
    private static final Logger LOGGER =LoggerFactory.getLogger(CustomerService.class);

    private static final QueryRunner QUERY_RUNNER =new QueryRunner();
    private static final ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<>();
    private static final BasicDataSource DATA_SOURCE;


    private final static String DRIVER;
    private final static String URL;
    private final static String USERNAME;
    private final static String PASSWORD;

    static{
        Properties conf =PropsUtil.loadProps("config.properties");
        DRIVER = conf.getProperty("jdbc.driver");
        URL = conf.getProperty("jdbc.url");
        USERNAME = conf.getProperty("jdbc.username");
        PASSWORD = conf.getProperty("jdbc.password");
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        DATA_SOURCE =new BasicDataSource();
        DATA_SOURCE.setDriverClassName(DRIVER);
        DATA_SOURCE.setUrl(URL);
        DATA_SOURCE.setUsername(USERNAME);
        DATA_SOURCE.setPassword(PASSWORD);
    }

    //获取数据库的连接
    public static Connection getConnection(){
        Connection conn=CONNECTION_HOLDER.get();
        if(conn==null) {
            try {
                conn =DATA_SOURCE.getConnection();
            } catch (SQLException e) {
                //e.printStackTrace();
                LOGGER.error("conn is fail!", e);
            }finally {
                CONNECTION_HOLDER.set(conn);
            }
        }
        return conn;
    }


    //查询实体列表
    public static <T> List<T> queryEntityList(Class<T> entityClass, String sql,Object... params){
        List<T> entityList = null;
        try{
            entityList =QUERY_RUNNER.query(getConnection(),sql,new BeanListHandler<T>(entityClass),params);
        }catch(SQLException e){
            e.printStackTrace();
        }finally {

        }
        return entityList;
    }
    //查询单个实体
    public static <T> T queryEntity(Class<T> entityClass, String sql,Object... params){
        T entity= null;
        try{
            entity =QUERY_RUNNER.query(getConnection(),sql,new BeanHandler<T>(entityClass),params);
        }catch(SQLException e){
            e.printStackTrace();
        }finally {

        }
        return entity;
    }

    //执行查询语句
    public static List<Map<String ,Object>> executeQuery(String sql , Object... params){
        List<Map<String ,Object>> result=null;
        try{
            Connection conn =getConnection();
            result = QUERY_RUNNER.query(conn,sql,new MapListHandler(),params);
        }catch(Exception e){
            e.printStackTrace();
        }
        return  result;
    }

    //执行更新语句
    public static int executeUpdate(String sql,Object... params){
        int rows=0;
        try{
            Connection conn=getConnection();
            rows = QUERY_RUNNER.update(conn,sql,params);
        }catch(SQLException e){
            e.printStackTrace();
        }finally {

            return rows;
        }
    }
    //插入实体
    public static <T> boolean insertEntity(Class<T> entityClass,Map<String,Object> fieldMap){
        if(CollectionUtil.isEmpty(fieldMap)){
            LOGGER.error("map is null");
            return false;
        }

        String sql ="insert into "+getTableName(entityClass);
        StringBuilder columns= new StringBuilder(" (");
        StringBuilder values= new StringBuilder(" (");

        for(String fieldName:fieldMap.keySet()){
           columns.append(fieldName+", ");
           values.append("?, ");
        }

        columns.replace(columns.lastIndexOf(","),columns.length(),") ");
        values.replace(values.lastIndexOf(","),values.length(),") ");
        sql += columns + " VALUES " +values;

        Object[] params =fieldMap.values().toArray();
        return executeUpdate(sql,params) == 1;
    }

    public static <T> boolean updateEntity(Class<T> entityClass,long id,Map<String ,Object> fieldMap){
        if(CollectionUtil.isEmpty(fieldMap)){
            LOGGER.error("can not update entity:fieldMap is empty");
            return false;
        }

        String sql="update "+getTableName(entityClass)+" set ";
        StringBuilder columns=new StringBuilder();
        for(String fieldName : fieldMap.keySet()){
            columns.append(fieldName).append("=?, ");
        }

        sql += columns.substring(0,columns.lastIndexOf(", "))+" where id=? ";

        List<Object> paramList=new ArrayList<Object>();
        paramList.addAll(fieldMap.values());
        paramList.add(id);
        Object[] params =paramList.toArray();

        return executeUpdate(sql,params) == 1;
    }

    //删除实体
    public static <T> boolean deleteEntity(Class<T> entityClass,long id){
        String sql ="delete from " + getTableName(entityClass)+" where id=?";
        return executeUpdate(sql,id)==1;
    }

    private static <T> String getTableName(Class<T> entityClass) {
        return entityClass.getSimpleName();
    }


}
